package com.za.plugin.transfer.form;


import cn.hutool.extra.pinyin.PinyinUtil;
import com.za.plugin.util.SqlUtil;
import com.za.plugin.util.StrUtil;

import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


// 把 select 中的 ' 变为 " ,对 case when 进行替换。因为达梦对单引号和双引号规定得很严格
public class SelectFormTransfer implements FormTransfer {

    @Override
    public boolean isSupport(String sql) {
        return sql.contains("select") && !sql.contains("insert into")
                && !sql.trim().startsWith("MERGE INTO") && !sql.trim().startsWith("merge into");
    }


    @Override
    public String transfer(String sql) {
        sql = sql.replaceAll(" \\|\\| ", " or ");
        // @i 这种 MySQL 变量一般作为序列号存在，默认一个 SQL 最多只有一个, "@i := @i + 1 i" 转为 "rownum i",其余的省略,比如 @cdate 这种
//        String varName = "";
        if (sql.contains("select") && !sql.contains("insert into")) {
            String varName = getVarName(sql);
            if (varName != null && varName.length() > 0) {
                String varDefine = findVarDefine2(sql, varName);
                String alias = getAlias(sql, varDefine);
                String newVarDefine = changeVarDefine(varDefine, varName, alias);
                sql = sql.replace(varDefine, newVarDefine);
                sql = sql.replaceAll(varName + "\\s*:=", "");
                sql = sql.replace(varName, alias + "." + "aliasName");

                sql = sql.replaceAll("[(]*\\s*"+alias + "\\.aliasName"+"\\s*"+"\\+"+"\\s*"+"1"+"\\s*[)]*"," rownum ");
            }
            int idx = sql.indexOf("select ");

            while (idx >= 0) {
                if (idx >= 1 && !isValidSelect(sql.charAt(idx - 1), sql, idx)) {
                    idx = sql.indexOf("select ", idx + 1);
                    continue;
                }
                StringBuilder sb = new StringBuilder().append(sql, 0, idx);
                sb.append(sql, idx, idx + "select ".length());
                // 把 "from(" 转为 "from (" ,格式兼容 ，之前没出现问题，稍微改了一点就出现了问题，改字符串问题即使是加个空格删个空格都要慎重
                sql = sql.replaceAll("from\\(", "from (");
                int right = sql.indexOf(" from ", idx);

                // 兼容 select ... union select ... from union select ... from
                int rightUnion = sql.indexOf(" union ", idx);
                if (rightUnion >= 0 && rightUnion < right) {
                    right = rightUnion;
                }
                while (!StrUtil.checkRightIdx(sql, idx, right)) {
                    right = sql.indexOf(" from ", right + 5);
                }
                // 解决 from tbl_select a   问题
                if (right == -1) {
                    right = idx + "select".length();
                }


                Map<String, String> selectProperties = SqlUtil.getSelectProperties(sql, idx);

                for (Map.Entry<String, String> entry : selectProperties.entrySet()) {
                    String key = entry.getKey().trim();
                    String value = entry.getValue().trim();
                    boolean isEqual = key.equals(value);
                    // key = "数据入库"
                    if (!hasPinYin(key)) {
                        // key = key.replace("\"", "");
                        // key concat_ws('_',plan_id,crop_subject_id)
                        if (key.charAt(0) == '\'') {
                            key = key.replace("'", "\"");
                        }
                    } else if (!key.contains("'")) {
                        key = "'" + key + "'";
                    }

                    if (value.startsWith("case when")) {
                        value = value.replace("\"", "'").toLowerCase();
                        value=value.replaceAll("\\s+then\\s+true\\s+"," then 'true' ")
                                .replaceAll("\\s+then\\s+false\\s+"," then 'false' ")
                                .replaceAll("\\s+else\\s+true\\s+"," else 'true' ")
                                .replaceAll("\\s+else\\s+false\\s+"," else 'false' ");
                    }
                    // 对 关键字加 "" 即可解决 语法分析出错
                    if (key.equalsIgnoreCase("desc") || key.equalsIgnoreCase("ref")) {
                        key = "\"" + key + "\"";
                    }
                    if (!isEqual) {
                        // 排除 a.* 这种情况
                        key = key.replaceAll("\\.\\s+", "\\.");
//                        role.id "role.id" 可以运行
//                        if (key.contains(".") && !key.contains(".*")) {
//                            // 别名里不能有 "." , 另外再把别名里的 " 去掉
//                            key = key.split("\\.")[1].replaceAll("\"", "");
//                        }
                        // 在 select 把 distinct 提到第一位，否则会报错
                        if (value.trim().startsWith("distinct")) {
                            sb.insert(idx + "select ".length(), " " + value + " " + key + " ,");
                        } /*else if (value.contains(":=") && value.contains("@")) {
                            // 处理 MySQL 的变量
//                            varName = getVarName(value);
//                            sb.append(" ").append("rownum").append(" ").append(key).append(" ,");
                        }*/ else {
                            sb.append(" ").append(value).append(" ").append(key).append(" ,");
                        }
                    } else {
                        if (value.trim().startsWith("distinct")) {
                            sb.insert(idx + "select ".length(), " " + key + " ,");
                        } else {
                            sb.append(" ").append(key).append(" ,");
                        }
                    }
                }
                if (right >= sql.length()) {
                    sql = sb.deleteCharAt(sb.length() - 1).toString();
                } else {
                    sql = sb.deleteCharAt(sb.length() - 1).append(sql.substring(right)).toString();
                }
                idx = sql.indexOf("select ", idx + "select ".length());
            }
        }
//        if (!"".equals(varName)) {
//            return findVarDefine(sql, varName);
//        }
        return sql;
    }

    private String changeVarDefine(String varDefine, String varName, String alias) {
        int idx = varDefine.indexOf("select ");
        int fromIdx = varDefine.indexOf(" from ");
        if (fromIdx == -1) {
            fromIdx = varDefine.length();
        }
        String substring = varDefine.substring(idx + 7, fromIdx);
        String[] split = substring.split(":=");
        if (fromIdx == varDefine.length()) {
            return "select " + split[1] + " aliasName ";
        }
        return "select " + split[1] + " aliasName " + varDefine.substring(fromIdx);
    }


    private String getAlias(String sql, String varDefine) {
        int idx = sql.indexOf(varDefine) + varDefine.length();
        while (idx < sql.length() && sql.charAt(idx) != ')') {
            idx++;
        }
        idx++;
        StringBuilder sb = new StringBuilder();
        while (idx < sql.length()) {
            if (sql.charAt(idx) == ' ' && sb.length() > 0) {
                break;
            }
            if (isValidChar(sql.charAt(idx))) {
                sb.append(sql.charAt(idx));
            }

            idx++;
        }
        return sb.toString();
//        Pattern compile = Pattern.compile(varDefine2 + "\\s*[)]*\\s+([a-zA-Z0-9]+)\\s+");
//        Matcher matcher = compile.matcher(sql);
//        if (matcher.find()) {
//            return matcher.group(1);
//        }
//        return "";
    }

    public static void main(String[] args) {
        String sql="select /*+ group_opt_flag(1)*/ a.id id , role.id 'role.id' , role.role_name role.rolename , role.role_desc role.roledesc , role.sort_num sortnum , role.create_time createtime , role.update_time updatetime from sys_user a left join sys_role role on a.role_id = role.id where a.id=? ";
        Map<String, String> selectProperties = SqlUtil.getSelectProperties(sql, 0);
        System.out.println(selectProperties);
    }

    private boolean isValidChar(char c) {
        return (c <= 'Z' && c >= 'A') || (c <= 'z' && c >= 'a') || (c <= '9' && c >= '0') || c == '_';
    }

    private String findVarDefine(String sql, String varName) {
        Pattern pattern = Pattern.compile("(,\\s*\\(\\s*select\\s+" + varName + ".*?\\)\\s*[a-zA-Z]+[a-zA-Z0-9]*)");
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            String group = matcher.group();
            return sql.replace(group, "");
        }
        return sql;
    }

    private String findVarDefine2(String sql, String varName) {
        Pattern pattern = Pattern.compile("select\\s+" + varName);
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            return SqlUtil.getValidSelect(sql, matcher.start());
        }
        return sql;
    }

    private String getVarName(String value) {
        Pattern pattern = Pattern.compile("(@[a-zA-Z]+[a-zA-Z0-9]*)");
        Matcher matcher = pattern.matcher(value);
        if (matcher.find()) {
            return matcher.group(1);
        }
        return "";
    }

    private boolean hasPinYin(String key) {
        if (key == null || key.length() == 0) {
            return false;
        }
        if (PinyinUtil.isChinese(key.charAt(0))) {
            return true;
        }
        return hasPinYin(key.substring(1));
    }


    private boolean isValidSelect(char ch, String sql, int left) {

        int end = SqlUtil.getSqlEnd(sql, left);
        String substring = sql.substring(left, end);
        // 一个合法的 select 后面肯定有 from ，排除 (select 1 )这种
        if (!substring.contains(" from ")) {
            return false;
        }
        if (ch <= 'z' && ch >= 'a') {
            return false;
        } else if (ch <= 'Z' && ch >= 'A') {
            return false;
        } else if (ch <= '9' && ch >= '0') {
            return false;
        } else if (ch == '_' || ch == '.' || ch == '\'' || ch == '\"') {
            return false;
        } else if (ch == ' ' || ch == '(' || ch == ')') {
            return true;
        }
        return false;
    }

    private boolean isValid(String str) {
        if (str == null || "".equals(str)) {
            return false;
        }
        char ch = str.charAt(0);
        if (ch >= 'a' && ch <= 'z') {
            return true;
        } else if (ch >= 'A' && ch <= 'Z') {
            return true;
        } else if (ch >= '0' && ch <= '9') {
            return true;
        } else if (ch == '\"' || ch == '\'' || ch == '.' || ch == '_' || ch == '*') {
            return isValid(str.substring(1));
        }
        return false;
    }
}
